package com.yxh.service;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

import com.yxh.R;
import com.yxh.ZybApplication;
import com.yxh.dto.CityDto;
import com.yxh.dto.UserDto;
import com.yxh.util.LogUtil;

public class DbSqliteService extends SQLiteOpenHelper {

	private static Context mContext = ZybApplication.getContext();

	private final static int DB_VERSION = 1;

	private final static String DB_NAME = "zyb.db";

	private final static String DB_PATH = mContext.getFilesDir()
			.getParentFile() + "/databases/" + DB_NAME;

	private final static String TABLE_CONFIG = "app_config";

	private final static String TABLE_USER = "app_user_info";

	private final static String TABLE_CITY = "app_city";
	
	private final static String TABLE_LESSON = "app_lesson";

	public static final String USER_ID = "uid";

	public static final String CITY_NAME = "cityName";

	public static final String CITY_CODE = "cityCode";

	public DbSqliteService(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
	}

	/** 数据库升级 **/
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// if (oldVersion < 2) {
		// db.execSQL("ALTER TABLE userInfo ADD COLUMN tqId TEXT;");
		// }
	}

	/** 初次创建数据库 ***/
	@Override
	public void onCreate(SQLiteDatabase db) {
		/** 也可代码创建 **/
	}

	/**
	 * 复制数据库到指定目录
	 * */
	public void createDataBase() throws IOException {
		try {
			copyDataBase();
		} catch (IOException e) {
			e.printStackTrace();
			throw new Error("Error copying database");
		}
	}

	public boolean getDataBase() {
		return checkDataBase();
	}

	/**
	 * 检测本地是否有数据库
	 * 
	 * @return true if it exists, false if it doesn't
	 */
	private boolean checkDataBase() {
		SQLiteDatabase checkDB = null;
		try {
			checkDB = SQLiteDatabase.openDatabase(DB_PATH, null,
					SQLiteDatabase.OPEN_READONLY);
		} catch (SQLiteException e) {
			LogUtil.e("检测本地是否有数据库异常:" + e);
			throw new Error("Database does't exist yet.");
		}
		if (checkDB != null) {
			checkDB.close();
		}
		LogUtil.e("本地是否有数据库 checkDB:" + checkDB);
		return checkDB != null ? true : false;
	}

	private void copyDataBase() throws IOException {
		// Open your local db as the input stream
		InputStream myInput = mContext.getResources()
				.openRawResource(R.raw.yxh);
		// Path to the just created empty db
		// Open the empty db as the output stream
		OutputStream myOutput = new FileOutputStream(DB_PATH);
		// transfer bytes from the inputfile to the outputfile
		byte[] buffer = new byte[1024];
		int length = 0;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}
		// Close the streams
		myInput.close();
		myOutput.flush();
		myOutput.close();
		myInput.close();
	}

	/******************************************************** 配置表sysconfig操作 ***************************************************/

	/***
	 * 删除配置
	 * 
	 * @param item
	 */
	public synchronized void removeConfigItem(String item) {
		SQLiteDatabase db = this.getWritableDatabase();

		try {
			db.delete(TABLE_CONFIG, "item='" + item + "'", null);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (db != null) {
				db.close();
			}
		}

	}

	/***
	 * 添加配置
	 * 
	 * @param item
	 * @param value
	 */
	public synchronized void addConfigItem(String item, String value) {
		SQLiteDatabase db = this.getWritableDatabase();
		try {
			db.delete(TABLE_CONFIG, "item='" + item + "'", null);
			String sql = "insert into " + TABLE_CONFIG
					+ "(item,value) values('" + item + "','" + value + "')";
			db.execSQL(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (db != null) {
				db.close();
			}

		}
	}

	/***
	 * 修改配置
	 * 
	 * @param item
	 * @param value
	 */
	public synchronized void modifyConfigItem(String item, String value) {
		SQLiteDatabase db = this.getWritableDatabase();
		try {

			String sql = "update " + TABLE_CONFIG + " set value='" + value
					+ "' where item='" + item + "'";
			db.execSQL(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (db != null) {
				db.close();
			}

		}
	}

	/***
	 * 获得配置
	 * 
	 * @param item
	 * @return
	 */
	public synchronized String getConfigItem(String item) {
		Cursor cursor = null;
		SQLiteDatabase db = null;
		String value = null;
		try {
			db = this.getReadableDatabase();
			cursor = db.query(TABLE_CONFIG, null, "item='" + item + "'", null,
					null, null, null);
			cursor.moveToFirst();
			if (!cursor.isAfterLast()) {
				value = cursor.getString(2);
				cursor.moveToNext();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (cursor != null) {
					cursor.close();
				}
				if (db != null) {
					db.close();
				}

			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return value;

	}

	/*************************************** 对User表进行操作 *********************************************/
	/**
	 * 对User表操作
	 * 
	 * @return
	 ****/

	// 添加一个用户
	public synchronized void insertUserInfo(UserDto dto) {
		if (isExitUserInfoById(dto.uid)) {
			updateUserDto(dto);
		} else {
			saveUserInfo(dto);
		}
	}

	// 插入用户
	public synchronized boolean saveUserInfo(UserDto dto) {

//		LogUtil.e("saveUserInfo  dto:" + dto);
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("uid", dto.uid);
		values.put("token", dto.token);
		values.put("userPic", dto.userPic);
		values.put("nickname", dto.nickname);
		values.put("userSex", dto.userSex);
		values.put("phoneNumber", dto.phoneNumber);
		values.put("userQrCode", dto.userQrCode);
		values.put("userType", dto.userType);
		values.put("isValid", dto.isValid);
		values.put("lastLoginDate", dto.lastLoginDate);
		values.put("loginCount", dto.loginCount);
		values.put("lastSearch", dto.lastSearch);
		values.put("loginIp", dto.loginIp);
		values.put("groupid", dto.groupid);
		values.put("msgCount", dto.msgCount);
		values.put("province", dto.province);
		values.put("city", dto.city);
		values.put("area", dto.area);
		long rowId = db.insert(TABLE_USER, null, values);
		close();
		return rowId > -1;
	}

	// 修改一个userDto
	public synchronized boolean updateUserDto(UserDto dto) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("uid", dto.uid);
		values.put("token", dto.token);
		values.put("userPic", dto.userPic);
		values.put("nickname", dto.nickname);
		values.put("userSex", dto.userSex);
		values.put("phoneNumber", dto.phoneNumber);
		values.put("userQrCode", dto.userQrCode);
		values.put("userType", dto.userType);
		values.put("isValid", dto.isValid);
		values.put("lastLoginDate", dto.lastLoginDate);
		values.put("loginCount", dto.loginCount);
		values.put("lastSearch", dto.lastSearch);
		values.put("loginIp", dto.loginIp);
		values.put("groupid", dto.groupid);
		values.put("msgCount", dto.msgCount);
		values.put("province", dto.province);
		values.put("city", dto.city);
		values.put("area", dto.area);
		int rows = db.update(TABLE_USER, values, "uid=" + dto.uid, null);
		close();
		return rows > 0;
	}

	/***
	 * 更新app信息
	 * 
	 * @param dto
	 * @return
	 */
	public synchronized boolean updateAppInfo(UserDto dto) {
		if (isExitUserInfoById(dto.uid)) {
			SQLiteDatabase db = this.getWritableDatabase();
			ContentValues values = new ContentValues();
			values.put("uid", dto.uid);
			values.put("nickname", dto.nickname);
			values.put("userSex", dto.userSex);
			values.put("phoneNumber", dto.phoneNumber);
			values.put("userPic", dto.userPic);
			values.put("province", dto.province);
			values.put("city", dto.city);
			values.put("area", dto.area);
			values.put("userQrCode", dto.userQrCode);
			int rows = db.update(TABLE_USER, values, "uid=" + dto.uid, null);
			close();
			return rows > 0;
		}
		return false;

	}

	// 查找主user
	public synchronized UserDto selectOneUserDto(String id) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(TABLE_USER, null, "uid=" + id, null, null,
				null, null);
		UserDto dto = null;
		try {
			cursor.moveToFirst();
			if (!cursor.isAfterLast()) {
//				LogUtil.e("---->");
				dto = new UserDto();
				dto.uid = cursor.getString(0);
				dto.token = cursor.getString(1);
				dto.userPic = cursor.getString(2);
				dto.nickname = cursor.getString(3);
				dto.userSex = cursor.getString(4);
				dto.phoneNumber = cursor.getString(5);
				dto.userQrCode = cursor.getString(6);

				dto.userType = cursor.getString(7);
				dto.isValid = cursor.getString(8);
				dto.lastLoginDate = cursor.getString(9);
				dto.loginCount = cursor.getString(10);
				dto.lastSearch = cursor.getString(11);
				dto.loginIp = cursor.getString(12);
				dto.groupid = cursor.getString(13);
				dto.msgCount = cursor.getString(14);

				dto.province = cursor.getString(15);
				dto.city = cursor.getString(16);
				dto.area = cursor.getString(17);

			}
		} catch (Exception e) {
			LogUtil.e(e.toString());
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			if (db != null) {
				db.close();
			}
		}
		return dto;
	}

	// 删除USER
	public synchronized void DeleteUserDtoById(int id) {
		SQLiteDatabase db = null;
		try {
			db = this.getWritableDatabase();
			String sql = "DELETE FROM " + TABLE_USER + " WHERE uid=" + id;
			db.execSQL(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (db != null) {
				db.close();
			}
		}
	}

	// 判断表里是否存在该用户
	public synchronized boolean isExitUserInfoById(String id) {
		SQLiteDatabase db = this.getReadableDatabase();
		String[] args = { id };
		Cursor cursor = db.query(TABLE_USER, null, "uid=?", args, null, null,
				null);
		try {
			cursor.moveToFirst();
			if (!cursor.isAfterLast()) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			if (db != null) {
				db.close();
			}
		}
		return false;
	}
	
//	
//	public synchronized List<CityDto> selectAllCityDtoList() {
//		List<CityDto> list = new ArrayList<CityDto>();
//		SQLiteDatabase db = this.getReadableDatabase();
//		Cursor cursor = db.query(TABLE_CITY, null, null,
//				null, null, null, null);
//		try {
//			cursor.moveToFirst();
//			while (!cursor.isAfterLast()) {
//				CityDto dto = new CityDto();
//				dto.region_id = String.valueOf(cursor.getInt(0));
//				dto.parent_id = cursor.getString(1);
//				dto.region_name = cursor.getString(2);
//				dto.region_type = cursor.getString(3);
//				dto.agency_id = cursor.getString(4);
//				dto.hasMore = cursor.getString(5);
//				list.add(dto);
//				cursor.moveToNext();
//			}
//		} catch (Exception e) {
//
//		} finally {
//			if (cursor != null) {
//				cursor.close();
//			}
//			if (db != null) {
//				db.close();
//			}
//		}
//		return list;
//	}

	
	/***
	 * 获取城市列表
	 */

	public synchronized List<CityDto> selectCityDtoList(String parentId) {
		List<CityDto> list = new ArrayList<CityDto>();
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(TABLE_CITY, null, "parent_id=" + parentId,
				null, null, null, null);
		try {
			cursor.moveToFirst();
			while (!cursor.isAfterLast()) {
				CityDto dto = new CityDto();
				dto.region_id = String.valueOf(cursor.getInt(0));
				dto.parent_id = cursor.getString(1);
				dto.region_name = cursor.getString(2);
				dto.region_type = cursor.getString(3);
				dto.agency_id = cursor.getString(4);
				dto.hasMore = cursor.getString(5);
				list.add(dto);
				cursor.moveToNext();
			}
		} catch (Exception e) {

		} finally {
			if (cursor != null) {
				cursor.close();
			}
			if (db != null) {
				db.close();
			}
		}
		return list;
	}

	/***
	 * 获取单个城市
	 */
	public synchronized CityDto selectOneCityDto(String region_id) {
		SQLiteDatabase db = this.getReadableDatabase();
		db.beginTransaction(); // 手动设置开始事务
		Cursor cursor = db.query(TABLE_CITY, null, "region_id=" + region_id,
				null, null, null, null);
		CityDto dto = null;
		try {
			cursor.moveToFirst();
			if (!cursor.isAfterLast()) {
				dto = new CityDto();
				dto.region_id = String.valueOf(cursor.getInt(0));
				dto.parent_id = cursor.getString(1);
				dto.region_name = cursor.getString(2);
				dto.region_type = cursor.getString(3);
				dto.agency_id = cursor.getString(4);
				dto.hasMore = cursor.getString(5);
			}
			db.setTransactionSuccessful();
		} catch (Exception e) {
			LogUtil.e(e.toString());
			e.printStackTrace();
		} finally {
			db.endTransaction();
			if (cursor != null) {
				cursor.close();
			}
			if (db != null) {
				db.close();
			}
		}
		return dto;
	}
	
	
//	/***
//	 * 更新City信息
//	 * @param dto
//	 * @return
//	 */
//	public synchronized boolean updateCityInfo(CityDto dto) {
//			SQLiteDatabase db = this.getWritableDatabase();
//			ContentValues values = new ContentValues();
//			values.put("has_more", dto.hasMore);
//			int rows = db.update(TABLE_CITY, values, "region_id=" + dto.region_id, null);
//			close();
//			return rows > 0;
//	}
	
	/***
	 * 判断当前城市是否有子城市
	 */
	public synchronized boolean isHasMoreCityById(String region_id) {
		SQLiteDatabase db = this.getReadableDatabase();
		db.beginTransaction(); // 手动设置开始事务
		String[] args = { region_id };
		Cursor cursor = db.query(TABLE_CITY, null, "parent_id=?", args, null,
				null, null);
		try {
			if (cursor.getCount() > 0) {
				return true;
			}
			db.setTransactionSuccessful();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();
			if (cursor != null) {
				cursor.close();
			}
			if (db != null) {
				db.close();
			}

		}
		return false;
	}
}